"""
统计分析服务
创建时间：2025-09-17
作者：开发团队
目的：处理统计分析相关业务逻辑
"""

from sqlalchemy.orm import Session
from sqlalchemy import and_, func, Integer
from datetime import date
from ..models.verification import VerificationRecord
from ..models.campus import Campus
from ..models.user import User
from ..models.special_visitor import SpecialVisitor
from ..utils.logger import get_logger

# 获取日志记录器
logger = get_logger(__name__)


def get_statistics_summary(
    db: Session,
    start_date: date,
    end_date: date,
    campus_id: int | None = None
) -> dict[str, object]:
    """
    获取统计摘要
    :param db: 数据库会话
    :param start_date: 开始日期
    :param end_date: 结束日期
    :param campus_id: 院区ID（可选）
    :return: 统计摘要
    """
    try:
        # 构建查询条件
        query = db.query(VerificationRecord)
        
        # 添加日期筛选
        query = query.filter(
            and_(
                func.date(VerificationRecord.verification_time) >= start_date,
                func.date(VerificationRecord.verification_time) <= end_date
            )
        )
        
        # 添加院区筛选
        if campus_id:
            query = query.join(User).filter(User.campus_id == campus_id)
        
        # 获取总核验数
        total_verifications = query.count()
        
        # 获取通过数
        pass_count = query.filter(VerificationRecord.result == "approved").count()
        
        # 获取拒绝数
        reject_count = query.filter(VerificationRecord.result == "rejected").count()
        
        # 获取特殊访客数
        special_visitor_query = db.query(SpecialVisitor)
        if campus_id:
            special_visitor_query = special_visitor_query.filter(SpecialVisitor.campus_id == campus_id)
        special_visitor_count = special_visitor_query.count()
        
        # 计算通过率
        pass_rate = round(pass_count / total_verifications * 100, 2) if total_verifications > 0 else 0
        
        return {
            "total_verifications": total_verifications,
            "pass_count": pass_count,
            "reject_count": reject_count,
            "special_visitor_count": special_visitor_count,
            "pass_rate": pass_rate
        }
    except Exception as e:
        logger.error(f"获取统计摘要失败: {str(e)}")
        raise


def get_daily_trend(
    db: Session,
    start_date: date,
    end_date: date,
    campus_id: int | None = None
) -> list[dict[str, object]]:
    """
    获取每日趋势
    :param db: 数据库会话
    :param start_date: 开始日期
    :param end_date: 结束日期
    :param campus_id: 院区ID（可选）
    :return: 每日趋势数据
    """
    try:
        # 构建查询
        query = db.query(
            func.date(VerificationRecord.verification_time).label('date'),
            func.count(VerificationRecord.id).label('total'),
            func.sum(
                func.cast(
                    VerificationRecord.result == 'approved',
                    Integer
                )
            ).label('pass_count'),
            func.sum(
                func.cast(
                    VerificationRecord.result == 'rejected',
                    Integer
                )
            ).label('reject_count')
        )
        
        # 添加日期筛选
        query = query.filter(
            and_(
                func.date(VerificationRecord.verification_time) >= start_date,
                func.date(VerificationRecord.verification_time) <= end_date
            )
        )
        
        # 添加院区筛选
        if campus_id:
            query = query.join(User).filter(User.campus_id == campus_id)
        
        # 按日期分组
        query = query.group_by(func.date(VerificationRecord.verification_time)).order_by('date')
        
        # 执行查询
        results = query.all()
        
        # 转换结果
        daily_trend: list[dict[str, object]] = []
        for row in results:
            daily_trend.append({
                "date": str(row.date) if row.date else '',
                "total": row.total or 0,
                "pass": row.pass_count or 0,
                "reject": row.reject_count or 0
            })
        
        return daily_trend
    except Exception as e:
        logger.error(f"获取每日趋势失败: {str(e)}")
        raise


def get_campus_stats(
    db: Session,
    start_date: date,
    end_date: date
) -> list[dict[str, object]]:
    """
    获取院区统计
    :param db: 数据库会话
    :param start_date: 开始日期
    :param end_date: 结束日期
    :return: 院区统计数据
    """
    try:
        # 获取所有院区
        campuses = db.query(Campus).all()
        
        campus_stats: list[dict[str, object]] = []
        for campus in campuses:
            # 构建查询
            query = db.query(VerificationRecord).join(User).filter(User.campus_id == campus.id)
            
            # 添加日期筛选
            query = query.filter(
                and_(
                    func.date(VerificationRecord.verification_time) >= start_date,
                    func.date(VerificationRecord.verification_time) <= end_date
                )
            )
            
            # 获取总核验数
            total = query.count()
            
            # 获取通过数
            pass_count = query.filter(VerificationRecord.result == "approved").count()
            
            # 获取拒绝数
            reject_count = query.filter(VerificationRecord.result == "rejected").count()
            
            campus_stats.append({
                "campus_name": campus.name,
                "total": total,
                "pass": pass_count,
                "reject": reject_count
            })
        
        return campus_stats
    except Exception as e:
        logger.error(f"获取院区统计失败: {str(e)}")
        raise


def get_special_visitor_stats(
    db: Session,
    start_date: date,
    end_date: date,
    campus_id: int | None = None
) -> dict[str, object]:
    """
    获取特殊访客统计
    :param db: 数据库会话
    :param start_date: 开始日期
    :param end_date: 结束日期
    :param campus_id: 院区ID（可选）
    :return: 特殊访客统计数据
    """
    try:
        # 构建查询
        query = db.query(SpecialVisitor)
        
        # 添加日期筛选（基于有效期）
        query = query.filter(
            and_(
                SpecialVisitor.valid_until >= start_date,
                SpecialVisitor.valid_until <= end_date
            )
        )
        
        # 添加院区筛选
        if campus_id:
            query = query.filter(SpecialVisitor.campus_id == campus_id)
        
        # 获取特殊访客总数
        total_count = query.count()
        
        # 按类型分组统计
        type_stats = db.query(
            SpecialVisitor.type,
            func.count(SpecialVisitor.id).label('count')
        ).filter(
            and_(
                SpecialVisitor.valid_until >= start_date,
                SpecialVisitor.valid_until <= end_date
            )
        )
        
        if campus_id:
            type_stats = type_stats.filter(SpecialVisitor.campus_id == campus_id)
            
        type_stats = type_stats.group_by(SpecialVisitor.type).all()
        
        # 转换类型统计结果
        type_distribution = {}
        for row in type_stats:
            type_distribution[row.type] = row.count
        
        return {
            "total_count": total_count,
            "type_distribution": type_distribution
        }
    except Exception as e:
        logger.error(f"获取特殊访客统计失败: {str(e)}")
        raise


def get_today_statistics(
    db: Session,
    guard_id: int | None = None
) -> dict[str, object]:
    """
    获取今日统计数据
    :param db: 数据库会话
    :param guard_id: 门卫ID（可选，用于获取指定门卫的统计数据）
    :return: 今日统计数据
    """
    try:
        # 获取今天的日期
        today = date.today()
        
        # 构建基础查询
        query = db.query(VerificationRecord)
        
        # 添加日期筛选（今天）
        query = query.filter(
            func.date(VerificationRecord.verification_time) == today
        )
        
        # 如果指定了门卫ID，添加门卫筛选
        if guard_id:
            query = query.filter(VerificationRecord.guard_id == guard_id)
        
        # 获取总核验数
        total_verifications = query.count()
        
        # 获取通过数
        pass_count = query.filter(VerificationRecord.result == "approved").count()
        
        # 获取拒绝数
        reject_count = query.filter(VerificationRecord.result == "rejected").count()
        
        # 获取特殊访客数
        special_visitor_query = db.query(SpecialVisitor)
        special_visitor_query = special_visitor_query.filter(
            func.date(SpecialVisitor.created_at) == today
        )
        special_visitor_count = special_visitor_query.count()
        
        # 获取我的核验数（如果指定了门卫ID）
        my_verifications = 0
        if guard_id:
            my_verifications = db.query(VerificationRecord).filter(
                and_(
                    VerificationRecord.guard_id == guard_id,
                    func.date(VerificationRecord.verification_time) == today
                )
            ).count()
        
        return {
            "total_verifications": total_verifications,
            "pass_count": pass_count,
            "reject_count": reject_count,
            "special_visitor_count": special_visitor_count,
            "my_verifications": my_verifications
        }
    except Exception as e:
        logger.error(f"获取今日统计数据失败: {str(e)}")
        raise